package com.wanshi.dao;

import com.wanshi.entity.PageInfo;
import com.wanshi.entity.User;
import com.wanshi.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
专门定义操作数据库的方法
 */
public class UserDao {

    //1 添加用户
    public void addUser(User user){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());

        String sql = "insert into user values(null,?,?,?,?,?,?)";
        try {
            runner.update(sql,user.getUsername(),user.getPassword(),user.getGender(),
                    user.getHobby(),user.getCity(),user.getIntroduce());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //2 根据用户名和密码查询用户
    public User findUserByNameAndPsd(String username,String password){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());

        String sql = "select  * from regist where username=? and password=?";
        try {
            return runner.query(sql,new BeanHandler<>(User.class),username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //3 根据用户名查找用户
    public User findUserByName(String username){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());

        String sql = "select  * from regist where username=?";
        try {
            return runner.query(sql,new BeanHandler<>(User.class),username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<User> list(){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select * from regist ";
        try {
            return runner.query(sql,new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void delUser(String id){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "delete from regist where id = ?";
        try {
            runner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public User findById(String id){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select * from regist where id =?";
        try {
            return runner.query(sql,new BeanHandler<>(User.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //根据user id修改用户
    public void updateUser(User user){
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "update regist set username=?,gender=?,hobby=?,city=?,introduce=? where id =?";
        try {
            runner.update(sql,user.getUsername(),user.getGender(),user.getHobby(),
                    user.getCity(),user.getIntroduce(),user.getId());
        } catch (SQLException e) {
            e.printStackTrace();

            throw new RuntimeException("用户名已经存在,修改失败!");
        }
    }

    public void deleteBatch(String[] ids) {
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "delete from regist where id in (";

        for(String id:ids){
            sql += id+",";
        }

        sql = sql.substring(0,sql.length()-1);
        sql += ")";

        System.out.println(sql);

        try {
            runner.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }

    public List<User> findByCond(User user) {
        //几个不确定, 不确定那个是第一个条件
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select * from regist where 3=3";

        List<Object> objs = new ArrayList<>();
        if(user.getUsername() != null && !user.getUsername().equals("")){
            sql += " and username like ? ";
            objs.add( "%"+ user.getUsername()+"%");
        }

        if(user.getGender() != null && !user.getGender().equals("")){
            sql += " and gender = ?";
            objs.add(user.getGender());
        }
        if(user.getCity() != null && !user.getCity().equals("")){
            sql += " and city = ?";
            objs.add(user.getCity());
        }

        System.out.println(sql);
        try {
            return runner.query(sql,new BeanListHandler<>(User.class),objs.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public List<User> findByPage(int i, int size) {
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select * from regist limit ?,?";
        try {
            return runner.query(sql,new BeanListHandler<>(User.class),i,size);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //定义方法 获取总记录数
    public Long getCount() {
        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select count(*) from regist";
        try {
            return (long)runner.query(sql,new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<User> findByPageAndCond(int i,int size,User user) {

        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());
        String sql = "select * from regist where 1=1";

        List<Object> objs = new ArrayList<>();
        if(user.getUsername() != null && !user.getUsername().equals("")){
            sql += " and username like ? ";
            objs.add( "%"+ user.getUsername()+"%");
        }

        if(user.getGender() != null && !user.getGender().equals("")){
            sql += " and gender = ?";
            objs.add(user.getGender());
        }
        if(user.getCity() != null && !user.getCity().equals("")){
            sql += " and city = ?";
            objs.add(user.getCity());
        }
        sql +=" limit ?,?";
        objs.add(i);
        objs.add(size);
        System.out.println(sql);
        try {
            return runner.query(sql,new BeanListHandler<>(User.class),objs.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Long getCondByCound(User user) {

        QueryRunner runner = new QueryRunner(JDBCUtil.getSource());

        String sql = "select count(*) from regist where 1=1 ";

        List<Object> objs = new ArrayList<>();
        if(user.getUsername() != null && !user.getUsername().equals("")){
            sql += " and username like ? ";
            objs.add( "%"+ user.getUsername()+"%");
        }

        if(user.getGender() != null && !user.getGender().equals("")){
            sql += " and gender = ?";
            objs.add(user.getGender());
        }
        if(user.getCity() != null && !user.getCity().equals("")){
            sql += " and city = ?";
            objs.add(user.getCity());
        }
        try {
            return (long)runner.query(sql,new ScalarHandler(),objs.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
